import os
import time
from pathlib import WindowsPath, Path

from PIL import JpegImagePlugin
from PyQt5.QtCore import QThread, pyqtSignal

import PrintSet
import util

JpegImagePlugin._getmp = lambda x: None
from copy import copy
import openpyxl
from openpyxl import load_workbook

# import xlsxwriter
MERGE_FILE_NAME = 'merged_excel.xlsx'
FILE = Path(__file__).resolve()
ROOT = FILE.parents[0]


def readFileList(filesExl):
    try:
        with open(filesExl, 'r') as f:
            list = f.readlines()
            util.my_logger.debug('Files name:')
            for name in list:
                util.my_logger.debug(name.strip())
            return [name.strip() for name in list]
    except Exception as e:
        util.my_logger.debug('打开文件错误：', e.__repr__())
        return []


def ConcatExcel(file_path_list, tarDir):
    wb = openpyxl.Workbook()
    ws = wb['Sheet']
    ws.title = 'Summary'
    util.my_logger.debug(file_path_list)
    for path in file_path_list:
        util.my_logger.debug(type(path))
        if type(path) == str:
            name = path.split(os.sep)[-1]
        elif type(path) == WindowsPath:
            name = path.name
        ws.append([name])
    ws.append([''])
    ws.append(['Count', len(file_path_list)])
    # timestr = time.strftime("%Y%m%d_%H%M", time.localtime())
    # fileName = timestr + '_'+MERGE_FILE_NAME
    targetPath = tarDir + os.sep + MERGE_FILE_NAME
    util.my_logger.debug('target Path: ' + targetPath)
    wb.save(targetPath)
    wb.close()
    util.my_logger.debug(len(file_path_list))
    util.comm_sig.my_sig.emit(0)
    for i, path in enumerate(file_path_list):
        if type(path) == str:
            sheet_name = path.split(os.sep)[-1].strip().replace('.xlsx', '')
        elif type(path) == WindowsPath:
            sheet_name = path.stem
        # copy sheet
        process_len = 100 * (i + 1) / len(file_path_list)
        util.my_logger.debug("process_len", process_len)
        util.comm_sig.my_sig.emit(process_len)
        replace_xls(path, targetPath, sheet_name)
    return targetPath


def get_print_settings():
    print_set = util.get_config(PrintSet.PRINT_SET)
    if not print_set:
        print_set = dict()
    return print_set


def replace_xls(src_file, tag_file, sheet_name):
    #        src_file是源xlsx文件，tag_file是目标xlsx文件，sheet_name是目标xlsx里的新sheet名称
    util.my_logger.debug("Start sheet %s copy from %s to %s" % (sheet_name, src_file, tag_file))
    try:
        wb = load_workbook(src_file)
    except Exception as e:
        util.my_logger.debug(e.__repr__())
        return
    wb2 = load_workbook(tag_file)

    ws = wb[wb.sheetnames[0]]
    ws2 = wb2.create_sheet(sheet_name)

    max_row = ws.max_row  # 最大行数
    max_column = ws.max_column  # 最大列数
    # 设置打印区域,页边距等
    # print_area = 'A1' + ":" + "%s%d" % (chr(max_column + 63).upper(), 1 + max_row)
    # ws2.print_area = print_area
    # # ws2.print_options.horizontalCentered = True
    # ws2.page_margins = openpyxl.worksheet.page.PageMargins(top=0.9, bottom=0.9, header=0.8, left=0.8,
    #                                                        right=0.8)

    ws2.page_setup.fitToPage = False
    ws2.sheet_properties.pageSetUpPr.fitToPage = True  # 自适应页面宽度
    ws2.print_options.horizontalCentered = True
    print_set = get_print_settings()
    cm_to_inch = 0.393700787402
    ws2.page_margins = openpyxl.worksheet.page.PageMargins(
        top=cm_to_inch * print_set.get(PrintSet.UP_SET, PrintSet.DEFAULT_UP_SET),
        bottom=cm_to_inch * print_set.get(PrintSet.BOTTOM_SET,
                                          PrintSet.DEFAULT_BOTTOM_SET),
        header=cm_to_inch * print_set.get(PrintSet.HEADER_SET,
                                          PrintSet.DEFAULT_HEADER_SET),
        footer=cm_to_inch * print_set.get(PrintSet.FOOTER_SET,
                                          PrintSet.DEFAULT_FOOTER_SET),
        left=cm_to_inch * print_set.get(PrintSet.LEFT_SET,
                                        PrintSet.DEFAULT_LEFT_SET),
        right=cm_to_inch * print_set.get(PrintSet.RIGHT_SET,
                                         PrintSet.DEFAULT_RIGHT_SET))  # 设置页边距(单位:英寸)
    # ws2.page_setup.fitToHeight = False
    # ws2.sheet_properties.pageSetUpPr.fitToPage = True
    # ws2.page_setup.fitToPage = True
    # print(str(max_column) + '  列数 ')
    # print(str(max_row) + '  行数 ')
    # print('print area : ' + print_area)

    wm = ws.merged_cells  # 开始处理合并单元格
    for i in wm:
        cell2 = str(i).replace('(<MergeCell ', '').replace('>,)', '')
        util.my_logger.debug("MergeCell : %s" % cell2)
        ws2.merge_cells(cell2)

    for m in range(1, max_row + 1):
        ws2.row_dimensions[m].height = ws.row_dimensions[m].height
        for n in range(1, 1 + max_column):
            if n < 27:
                c = chr(n + 64).upper()  # ASCII字符,chr(65)='A'
            else:
                if n < 677:
                    c = chr(divmod(n, 26)[0] + 64) + chr(divmod(n, 26)[1] + 64)
                else:
                    c = chr(divmod(n, 676)[0] + 64) + chr(divmod(divmod(n, 676)[1], 26)[0] + 64) + chr(
                        divmod(divmod(n, 676)[1], 26)[1] + 64)
            i = '%s%d' % (c, m)  # 单元格编号
            if m == 1:
                #				 print("Modify column %s width from %d to %d" % (n, ws2.column_dimensions[c].width ,ws.column_dimensions[c].width))
                ws2.column_dimensions[c].width = ws.column_dimensions[c].width
            try:
                getattr(ws.cell(row=m, column=n), "value")
                cell1 = ws[i]  # 获取data单元格数据
                ws2[i].value = cell1.value  # 赋值到ws2单元格
                if cell1.has_style:  # 拷贝格式
                    ws2[i].font = copy(cell1.font)
                    ws2[i].border = copy(cell1.border)
                    ws2[i].fill = copy(cell1.fill)
                    ws2[i].number_format = copy(cell1.number_format)
                    ws2[i].protection = copy(cell1.protection)
                    ws2[i].alignment = copy(cell1.alignment)
            except AttributeError as e:
                util.my_logger.debug("cell(%s) is %s" % (i, e))
                continue
    for i in wm:
        cell2 = str(i).replace('(<MergeCell ', '').replace('>,)', '')
        util.my_logger.debug("MergeCell : %s" % cell2)
        ws2.merge_cells(cell2)
    # 图片处理
    for image in ws._images:
        ws2.add_image(image)
        # c = chr(image.anchor._from.col+1+64).upper()
        # location = "%s%d"%(c,image.anchor._from.row+1)
        # print(image.anchor._from.row )
        # print(image.anchor._from.col )
        # print(image)
    wb2.save(tag_file)

    wb2.close()
    wb.close()


def getFiles(rootDir, names):
    if not names or len(names) == 0:
        return
    path = rootDir
    util.my_logger.debug('Now directory: ' + rootDir)
    exist_file_paths = []
    not_found_files = []
    for name in names:
        for file in os.listdir(path):
            # 当前目录路径 print(root)
            # 当前路径下所有子目录 print(dirs)
            # 当前路径下所有非目录子文件 print(files)
            # 打印包含names的file
            file_path = os.path.join(path, file)
            if name in file:
                util.my_logger.debug(file_path)
                exist_file_paths.append(file_path)
    # if len(names) != len(existFilePaths):
    #     print('Warning: {} files were not found!!!'.format(len(names) - len(existFilePaths)))
    # print(str(existFilePaths))
    for name in names:
        if name not in str(exist_file_paths):
            not_found_files.append(name)
    return exist_file_paths, not_found_files


def checkCopyComplete(names, existFilePaths, NotFoundFiles):
    if len(names) != len(existFilePaths):
        util.my_logger.debug('Warning: {} files were not found!!!'.format(len(names) - len(existFilePaths)))

    for i in NotFoundFiles:
        util.my_logger.debug(i + ' was not found')
    return NotFoundFiles


def StartWork(rootDir, fileListPath, tarDir):
    if not rootDir:
        target_file = ConcatExcel(fileListPath, tarDir)
        return target_file, {}
    else:
        names = readFileList(fileListPath)
        exist_file_paths, not_found_files = getFiles(rootDir, names)
        target_file = ConcatExcel(exist_file_paths, tarDir)
        checkCopyComplete(names, exist_file_paths, not_found_files)
        return target_file, not_found_files


class ExcelThread(QThread):
    finish_signal = pyqtSignal(dict)

    def __init__(self):
        super(ExcelThread, self).__init__()
        self.rootDir = ROOT
        self.fileListPath = {}
        self.target_dir = ROOT

    def run(self) -> None:
        StartWork(self.rootDir, self.fileListPath, self.target_dir)



# if __name__ == '__main__':
#     path = r'C:\Users\80674\Desktop\测试\源文件夹'
#     fileList = r'C:\Users\80674\Desktop\测试\目标列表.txt'
#     tarDir = r'C:\Users\80674\Desktop'
#     StartWork(path, fileList, tarDir)
